package com.topideal.supplychain.hades.mapper;

import com.topideal.supplychain.hades.dto.PersonalViewDto;
import com.topideal.supplychain.hades.viewfilter.BasicInfoFilter;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 个人工时统计sql
 *
 * @author xuxiaoyan
 * @date 2018-09-13 18:04
 */
@Mapper
public interface PersonalListMapper {


    /**
     * 个人工时统计 按人员维度+jira创建时间 汇总所有登记的工时
     *
     * @param basicInfoFilter
     * @return
     */
    @Select("<script> " +
            "select w.AUTHOR as userKey, SUM(w.timeworked)/3600 as timeWorked "
            + "from worklog w "
            + "inner join jiraissue js on js.ID = w.issueid "
            + " <where> "
            + " <if test=\"userKey != null and userKey != '' \"> and w.AUTHOR = #{userKey} </if> "
            + "	<if test=\"createDateFromStr != null and createDateFromStr != '' \"> and js.CREATED >= #{createDateFromStr}</if> "
            + "	<if test=\"createDateToStr != null and createDateToStr != '' \"> and #{createDateToStr} >= js.CREATED</if> "
            + " </where> "
            + " group by w.AUTHOR"
            + " </script> ")
    List<PersonalViewDto> selectTimeSummary(BasicInfoFilter basicInfoFilter);


    /**
     * 人员工时统计详情 合并逻辑
     *
     * @param filter
     * @return
     */
    @Select("<script> " +
            "select w.AUTHOR as userKey, concat(p.pkey,\"-\",js.issuenum) as jiraNum, p.pname as pName, cp.cpn as cName, js.SUMMARY as title, "
            + "js.issuetype as issueType, js.issuestatus as issueStatus, js.CREATED as issueCreateTime, SUM(w.timeworked)/3600 as timeWorked "
            + "from worklog w "
            + "inner join jiraissue js on js.ID = w.issueid "
            + "left join (SELECT na.SOURCE_NODE_ID jid, c.cname cpn "
            + "FROM component c INNER join nodeassociation na on c.ID = na.SINK_NODE_ID "
            + "WHERE na.ASSOCIATION_TYPE = 'IssueComponent') cp on cp.jid = js.id "
            + "inner join project p on p.ID = js.PROJECT "
            + " <where> "
            + " <if test=\"userKey != null and userKey != '' \"> and w.AUTHOR = #{userKey} </if> "
            + "	<if test=\"createDateFromStr != null and createDateFromStr != '' \"> and js.CREATED >= #{createDateFromStr}</if>"
            + "	<if test=\"createDateToStr != null and createDateToStr != '' \"> and #{createDateToStr} >= js.CREATED</if>"
            + " </where> "
            + "group by js.issuenum"
            + " </script> ")
    List<PersonalViewDto> selectTimeDetail(BasicInfoFilter filter);

    /**
     * 人员工时统计详情 不合并逻辑
     *
     * @param filter
     * @return
     */
    @Select("<script> " +
            "select w.AUTHOR as userKey, concat(p.pkey,\"-\",js.issuenum) as jiraNum, p.pname as pName, cp.cpn as cName, js.SUMMARY as title, "
            + "js.issuetype as issueType, js.issuestatus as issueStatus, js.CREATED as issueCreateTime, w.timeworked/3600 as timeWorked "
            + "from worklog w "
            + "inner join jiraissue js on js.ID = w.issueid "
            + "left join (SELECT na.SOURCE_NODE_ID jid, c.cname cpn "
            + "FROM component c INNER join nodeassociation na on c.ID = na.SINK_NODE_ID "
            + "WHERE na.ASSOCIATION_TYPE = 'IssueComponent') cp on cp.jid = js.id "
            + "inner join project p on p.ID = js.PROJECT "
            + " <where> "
            + " <if test=\"userKey != null and userKey != '' \"> and w.AUTHOR = #{userKey} </if> "
            + "	<if test=\"createDateFromStr != null and createDateFromStr != '' \"> and js.CREATED >= #{createDateFromStr}</if>"
            + "	<if test=\"createDateToStr != null and createDateToStr != '' \"> and #{createDateToStr} >= js.CREATED</if>"
            + " </where> "
            + " order by js.issuenum ASC"
            + " </script> ")
    List<PersonalViewDto> selectEveryTimeDetail(BasicInfoFilter filter);
}
